/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.ufpb.halloc.dados;

import br.com.ufpb.halloc.conexaoDB.Pool;
import br.com.ufpb.halloc.conexaoDB.PoolIF;
import br.com.ufpb.halloc.exceptions.DisciplinaInexistenteException;
import br.com.ufpb.halloc.exceptions.DisciplinaInvalidaException;
import br.com.ufpb.halloc.negocio.Disciplina;
import br.com.ufpb.halloc.negocio.Horario;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/**
 *
 * @author leonardo
 */
public class DisciplinaDAOPostgreSQL implements DisciplinaDAO {

    
    private PoolIF pool;
    
    
    /* SINGLETON */
    private static DisciplinaDAOPostgreSQL daoPostgres;  
    
    public static DisciplinaDAOPostgreSQL getInstance(){
        if (daoPostgres == null)
            daoPostgres = new DisciplinaDAOPostgreSQL();
        return daoPostgres;
    }
    
    /* COSTRUTOR */
    private DisciplinaDAOPostgreSQL(){
        this.pool = new Pool();
    }
    
    
    @Override
    public boolean inserir(Disciplina disc) throws DisciplinaInvalidaException {
        
        Connection conn = pool.getConnection();
       
        PreparedStatement ps;
        
        String update = "INSERT INTO disciplina (nomedisciplina, creditos, nrdepartamento, coddisciplina) VALUES (?,?,?,?);";
        
        try {
            ps = conn.prepareStatement(update);
            ps.setString(1, disc.getNome());
            ps.setInt(2, disc.getCreditos());
            ps.setString(3, disc.getNumdpto());
            ps.setString(4, disc.getID());            
            ps.execute();
            ps.close();

        } catch (SQLException ex) {
            Logger.getLogger(DisciplinaDAOPostgreSQL.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(conn);
        }
        return false;
    }
    
    public boolean remover(String id) throws DisciplinaInexistenteException {
        
        Connection conn = this.pool.getConnection();
        PreparedStatement ps;
        
        String commandSQL = "DELETE FROM disciplina WHERE coddisciplina = '"+ id + "';";
        
        try {            
            ps = conn.prepareStatement(commandSQL);
            ps.executeUpdate();
            ps.close();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(DisciplinaDAOPostgreSQL.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            this.pool.liberarConnection(conn);
        }
        return false;        
    }

    @Override
    public boolean remover(Disciplina disc) throws DisciplinaInexistenteException {        
        return false;        
    }

    @Override
    public Disciplina pesquisarID(String id) throws DisciplinaInexistenteException {
        
        Connection conn = this.pool.getConnection();
        PreparedStatement ps;
        Disciplina disciplina = null;
        String consultaSQL = "SELECT * FROM disciplina WHERE coddisciplina = '"+ id + "';";
        try {            
            ps = conn.prepareStatement(consultaSQL);
            ResultSet rset = ps.executeQuery();
            while (rset.next()){
                disciplina = new Disciplina(rset.getString("coddisciplina"), 
                        rset.getString("nomedisciplina"), rset.getString("nrdepartamento"),
                        rset.getInt("creditos"));                
            }
            if (disciplina == null) {
               throw new DisciplinaInexistenteException("Não existe nenhuma Disciplina com o código " + id + " cadastrada.");
            }
            ps.close();
        } catch (SQLException ex) {
            Logger.getLogger(DisciplinaDAOPostgreSQL.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            this.pool.liberarConnection(conn);
        }        
        return disciplina;
    }

    @Override
    public List<Disciplina> pesquisar(String nome) throws DisciplinaInvalidaException {
        
        Connection conn = this.pool.getConnection();
        PreparedStatement ps;
        List<Disciplina> disciplinas = new ArrayList<Disciplina>();
        String consultaSQL = "SELECT * FROM disciplina WHERE LOWER(nomedisciplina) LIKE '"+nome.toLowerCase()+"%';";
        try {
            ps = conn.prepareStatement(consultaSQL);
            ResultSet rset = ps.executeQuery();
            while(rset.next()){
                Disciplina d = new Disciplina(rset.getString("coddisciplina"), 
                        rset.getString("nomedisciplina"), rset.getString("nrdepartamento"),
                        rset.getInt("creditos"));
                disciplinas.add(d);
            }
            ps.close();
        } catch (SQLException ex) {
            Logger.getLogger(DisciplinaDAOPostgreSQL.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            this.pool.liberarConnection(conn);
        }        
        return disciplinas;
        
    }

    @Override
    public boolean atualizar(Disciplina disc) throws DisciplinaInexistenteException {
        
        Connection conn = this.pool.getConnection();        
        PreparedStatement ps;
        
        String command = "UPDATE disciplina SET nomedisciplina = '" + disc.getNome() + "', creditos = " + disc.getCreditos() +
                ", nrdepartamento = '" + disc.getNumdpto() + "' where coddisciplina = '"+ disc.getID() + "';";
        try {            
            ps = conn.prepareStatement(command);
            ps.executeUpdate();
            ps.close();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(DisciplinaDAOPostgreSQL.class.getName()).log(Level.SEVERE, null, ex);
            throw new DisciplinaInexistenteException("Opa! Verifique se esta disciplina existe antes de atualizar.");
        } finally {
            this.pool.liberarConnection(conn);
        }
    }
    
    
    public List<Disciplina> getTodasAsDisciplinasCadastradasNoBanco(){
        
        Connection conn = this.pool.getConnection();
        
        List<Disciplina> disciplinas = new ArrayList<Disciplina>();
        
        PreparedStatement ps;
        
        String consultaSQL = "SELECT * FROM disciplina ORDER BY nomedisciplina;";
        
        try {
            
            ps = conn.prepareStatement(consultaSQL);
            ResultSet rset = ps.executeQuery();
            while(rset.next()){
                Disciplina d = new Disciplina(rset.getString("coddisciplina"), 
                        rset.getString("nomedisciplina"), rset.getString("nrdepartamento"),
                        rset.getInt("creditos"));
                disciplinas.add(d);
            }            
            ps.close();
        } catch (SQLException ex) {
            Logger.getLogger(DisciplinaDAOPostgreSQL.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(conn);
        }  
        return disciplinas;
    }
    
    
}
